package com.ichina.chat.dao.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.ichina.chat.dao.TrafficDao;
import com.ichina.chat.vo.Traffic;
import com.ichina.chat.vo.UserInfo;

@Component
@Transactional
public class TrafficDaoimpl implements TrafficDao {

	@Resource
	SessionFactory sessionFactory;
	private Session session;
	private String hql = "";
	private Query query = null;

	public void addTraffic(Traffic u) throws Exception {
		// 持久化,相当于save
		sessionFactory.getCurrentSession().persist(u);
	}

	public boolean deleteTraffic(String ip) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	public int queryIpCountByIp(String ip) throws Exception {

		return 0;
	}

	public boolean queryIpTime(String ip) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	public int queryUserByIp(String ip) throws Exception {
		// TODO Auto-generated method stub
		return 0;
	}

	// 今天的单天访问量 IP数,IP以日(00:00-24:00,为一个IP)划分
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public int queryUserByToday(Date time) throws Exception {
		int total = 0;
		hql = "from Traffic  where DATE_FORMAT(visitDate,'%Y-%m-%d')=? GROUP  by ip";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String d = sdf.format(time);
		session = sessionFactory.openSession();
		query = session.createQuery(hql).setString(0, d);
		List<UserInfo> list = query.list();
		total = list.size();
		return total;
	}

	// 昨天的单天访问数,IP数,IP以日(00:00-24:00,为一个IP)划分
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public int queryUserByYesterday(Date time) throws Exception {
		int total = 0;
		hql = "SELECT * FROM Traffic WHERE TO_DAYS(now()) - TO_DAYS(visitDate) =1 GROUP  by ip";
		session = sessionFactory.openSession();
		query = session.createSQLQuery(hql);
		List l = query.list();
		total = l.size();
		return total;
	}

	// 总的IP数,IP以日(00:00-24:00,为一个IP)划分
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public int queryUserTotal() throws Exception {
		int total = 0;
		hql = "from Traffic group by DATE_FORMAT(visitDate,'%Y-%m-%d'),ip";
		session = sessionFactory.openSession();
		query = session.createQuery(hql);
		total = query.list().size();
		return total;
	}

	public String queryUserUrl(String ip) throws Exception {
		// TODO Auto-generated method stub 
		return null;
	}

	// 上周的单周访问量 IP数,IP以日(00:00-24:00,为一个IP)划分
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public int queryUserByLastWeek(Date time) throws Exception {
		int total = 0;
		hql = "select ip from traffic where visitDate>=date_add(now(),interval -(8 + weekday(now())) day) and visitDate<=date_add(now(),interval -(1 + weekday(now())) day) group by DATE_FORMAT(visitDate,'%Y-%m-%d'),ip";
		session = sessionFactory.openSession();
		query = session.createSQLQuery(hql);
		total = query.list().size();
		return total;
	}
	
	// 上个月的访问数,IP数,IP以日(00:00-24:00,为一个IP)划分
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public int queryUsrByLastMonth(Date time) throws Exception {
		int total = 0;
		hql = "SELECT ip FROM Traffic WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format(visitDate, '%Y%m' ) ) =1 group by DATE_FORMAT(visitDate,'%Y-%m-%d'),ip";
		session = sessionFactory.openSession();
		query = session.createSQLQuery(hql);
		total = query.list().size();
		return total;
	}
	
	// 本周的单周访问量 IP数,IP以日(00:00-24:00,为一个IP)划分
	public int queryUserByThisWeek(Date time) throws Exception {
		int total = 0;
		hql = "select ip from traffic where WEEKOFYEAR(visitDate)=WEEKOFYEAR(NOW()) group by DATE_FORMAT(visitDate,'%Y-%m-%d'),ip";
		session = sessionFactory.openSession();
		query = session.createSQLQuery(hql);
		total = query.list().size();
		return total;
	}

	// 本月的访问数,IP数,IP以日(00:00-24:00,为一个IP)划分
	public int queryUsrByThisMonth(Date time) throws Exception {
		int total = 0;
		hql = "select ip from traffic where MONTH(visitDate)=MONTH(NOW()) and year(visitDate)=year(now()) group by DATE_FORMAT(visitDate,'%Y-%m-%d'),ip";
		session = sessionFactory.openSession();
		query = session.createSQLQuery(hql);
		total = query.list().size();
		return total;
	}

	public boolean updateTraffic(Traffic u) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean updateUer(int id) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	// 总的VP数,即总的点击数
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public int queryTotalClick() throws Exception {
		hql = "from Traffic";
		session = sessionFactory.getCurrentSession();
		query = session.createQuery(hql);
		return query.list().size();
	}

	// 查找关键字排行榜
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public Map<String,Integer> queryTopkeywords() throws Exception {
		List lt = new ArrayList();
		LinkedHashMap<String,Integer> kewords = new LinkedHashMap<String,Integer>();
		hql = "select keywords from traffic GROUP by keywords order by count(keywords) desc ";
		session = sessionFactory.openSession();
		query = session.createSQLQuery(hql);
		lt = query.list();
			for (int i = 0; i < lt.size(); i++) {
				if (("").equals(lt.get(i))) {
					//lt.remove(i);
				}
				else if (null == lt.get(i)) {
					//lt.remove(i);
				} else {
					hql = "select count(keywords) as c from traffic where keywords='"+lt.get(i).toString()+"'";
					query = session.createSQLQuery(hql).addScalar("c", Hibernate.INTEGER);
					kewords.put(lt.get(i).toString(), (Integer)query.uniqueResult());
				}
			}
		return kewords;
	}

	// 查找搜索引擎排行榜
	public Map<String, Integer> querySearchEngine() throws Exception {
		List lt = new ArrayList();
		LinkedHashMap<String,Integer> searchEngine = new LinkedHashMap<String,Integer>();
		hql = "select SourceURL from traffic GROUP by SourceURL having SourceURL not like '%chat%' order by count(SourceURL) desc ";
		session = sessionFactory.openSession();
		query = session.createSQLQuery(hql);
		lt = query.list();
			for (int i = 0; i < lt.size(); i++) {
				if (("").equals(lt.get(i))) {
					//lt.remove(i);
				}
				else if (null == lt.get(i)) {
					//lt.remove(i);
				} else {
					hql = "select count(SourceURL) as c from traffic where SourceURL='"+lt.get(i).toString()+"'";
					query = session.createSQLQuery(hql).addScalar("c", Hibernate.INTEGER);
					searchEngine.put(lt.get(i).toString(), (Integer)query.uniqueResult());
				}
			}
		return searchEngine;
	}

	// 查找接入商排行榜
	public Map<String, Integer> queryConnectBusiness() throws Exception {
		List lt = new ArrayList();
		LinkedHashMap<String,Integer> connectBusiness = new LinkedHashMap<String,Integer>();
		hql = "select connectBusiness from traffic GROUP by connectBusiness order by count(connectBusiness) desc ";
		session = sessionFactory.openSession();
		query = session.createSQLQuery(hql);
		lt = query.list();
			for (int i = 0; i < lt.size(); i++) {
					hql = "select count(connectBusiness) as c from traffic where connectBusiness='"+lt.get(i).toString()+"'";
					query = session.createSQLQuery(hql).addScalar("c", Hibernate.INTEGER);
					connectBusiness.put(lt.get(i).toString(), (Integer)query.uniqueResult());
			}
		return connectBusiness;
	}

	// 查找时间段排行榜
	public Map<String, Integer> queryVisitDate() throws Exception {
		List lt = new ArrayList();
		LinkedHashMap<String,Integer> visitDate = new LinkedHashMap<String,Integer>();
		hql = "select DATE_FORMAT(visitDate,'%k') as c from traffic group by DATE_FORMAT(visitDate,'%k')order by count(DATE_FORMAT(visitDate,'%k')) desc";
		session = sessionFactory.openSession();
		query = session.createSQLQuery(hql).addScalar("c", Hibernate.INTEGER);
		lt = query.list();
			for (int i = 0; i < lt.size(); i++) {
					hql = "select count(visitDate) as c from traffic where DATE_FORMAT(visitDate,'%k')='"+lt.get(i).toString()+"' group by DATE_FORMAT(visitDate,'%k')";
					query = session.createSQLQuery(hql).addScalar("c", Hibernate.INTEGER);
					visitDate.put(lt.get(i).toString(), (Integer)query.uniqueResult());
			}
		return visitDate;
	}

	// 查找最高独立IP访问日期
	public Map<String, Integer> queryTopDistinctIpDate() throws Exception {
		List lt = new ArrayList();
		LinkedHashMap<String,Integer> topDistinctIpDate = new LinkedHashMap<String,Integer>();
		hql="select DATE_FORMAT(visitDate,'%Y-%m-%d') as d from traffic group by DATE_FORMAT(visitDate,'%Y-%m-%d') order by count(distinct ip) desc LIMIT 0,1";
		session = sessionFactory.getCurrentSession();
		query = session.createSQLQuery(hql).addScalar("d",Hibernate.STRING);
		lt = query.list();
		for (int i = 0; i < lt.size(); i++) {
				hql = "select count(distinct ip) as c from traffic where DATE_FORMAT(visitDate,'%Y-%m-%d')='"+lt.get(i).toString()+"' group by DATE_FORMAT(visitDate,'%Y-%m-%d') order by count(distinct ip) desc";
				query = session.createSQLQuery(hql).addScalar("c", Hibernate.INTEGER);
				topDistinctIpDate.put(lt.get(i).toString(), (Integer)query.uniqueResult());
		}
		return topDistinctIpDate;
	}

	// 查找最高浏览量日期
	public Map<String, Integer> queryTopCountIpDate() throws Exception {
		List lt = new ArrayList();
		LinkedHashMap<String,Integer> topDistinctIpDate = new LinkedHashMap<String,Integer>();
		hql="select DATE_FORMAT(visitDate,'%Y-%m-%d') as d from traffic group by DATE_FORMAT(visitDate,'%Y-%m-%d') order by count(distinct ip) desc LIMIT 0,1";
		session = sessionFactory.getCurrentSession();
		query = session.createSQLQuery(hql).addScalar("d",Hibernate.STRING);
		lt = query.list();
		for (int i = 0; i < lt.size(); i++) {
				hql = "select count(ip) as c from traffic where DATE_FORMAT(visitDate,'%Y-%m-%d')='"+lt.get(i).toString()+"' group by DATE_FORMAT(visitDate,'%Y-%m-%d') order by count(ip) desc";
				query = session.createSQLQuery(hql).addScalar("c", Hibernate.INTEGER);
				topDistinctIpDate.put(lt.get(i).toString(), (Integer)query.uniqueResult());
		}
		return topDistinctIpDate;
	}

	//按选定日期范围查询独立IP访问量
	public Map<String, Integer> queryChooseDateCountIp(String startTime,String endTime) throws Exception {
		List lt = new ArrayList();
		LinkedHashMap<String,Integer> chooseDateCountIp = new LinkedHashMap<String,Integer>();
		//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		hql="select DATE_FORMAT(visitDate,'%k') as d from traffic where DATE_FORMAT(visitDate,'%Y-%m-%d') >= '"+startTime+"' and DATE_FORMAT(visitDate,'%Y-%m-%d') <= '"+endTime+"' group by DATE_FORMAT(visitDate,'%k')";
		session = sessionFactory.getCurrentSession();
		query = session.createSQLQuery(hql).addScalar("d",Hibernate.STRING);
		lt = query.list();
		for (int i = 0; i < lt.size(); i++) {
				hql = "select count(distinct ip) as c from traffic where DATE_FORMAT(visitDate,'%Y-%m-%d') >= '"+startTime+"' and DATE_FORMAT(visitDate,'%Y-%m-%d') <= '"+endTime+"' and DATE_FORMAT(visitDate,'%k')='"+lt.get(i).toString()+"' group by DATE_FORMAT(visitDate,'%k')";
				System.out.println(hql);
				query = session.createSQLQuery(hql).addScalar("c", Hibernate.INTEGER);
				chooseDateCountIp.put(lt.get(i).toString(), (Integer)query.uniqueResult());
		}
		return chooseDateCountIp;
	}

	public Map<String, Integer> queryPageTatalPv() throws Exception {
		List lt = new ArrayList();
		LinkedHashMap<String,Integer> topDistinctIpDate = new LinkedHashMap<String,Integer>();
		hql="SELECT targeturl from traffic group by targeturl order by count(targeturl)";
		session = sessionFactory.getCurrentSession();
		query = session.createSQLQuery(hql).addScalar("d",Hibernate.STRING);
		lt = query.list();
		for (int i = 0; i < lt.size(); i++) {
				hql = "select count(targeturl) as c from traffic order by count(targeturl) desc";
				query = session.createSQLQuery(hql).addScalar("c", Hibernate.INTEGER);
				topDistinctIpDate.put(lt.get(i).toString(), (Integer)query.uniqueResult());
		}
		return topDistinctIpDate;
	}
}
